﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using Agresso.Interface.Layer.SQL;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class agldimvalueService
    {
         DataBaseConnectivity m_dataBaseConectivity;
         public agldimvalueService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public agldimvalue GetAglDimValueVesselByDescription(string name)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                //SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescription(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionLike(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = name;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueVesselByDescription(string name, string client)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                //SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionAndClient(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionAndClientLike(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = name.Trim();
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = client;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public IList GetAglDimValueVesselByDescriptionList(string name)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                //SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionLike(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                //SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionS1(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescription(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = name;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new agldimvalue(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public IList GetAglDimValueVesselByDescriptionListLike(string name)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDescriptionLikeSearch(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = name;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new agldimvalue(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public IList GetAglDimValueProfitCentreByDescriptionListLike(string name)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueProfitCentreByDescriptionListLike(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = name;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new agldimvalue(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueVesselByDimValue(string dimval)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimval;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueVesselByDimValueAndClient(string dimval, string client)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueVesselByDimValAndClient(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimval;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = client;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueProfitCentreByDescription(string name)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueProfitCentreByDescription(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = name;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueProfitCentreByDimValue(string dimValue)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueProfitCentreByDimValue(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimValue;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueProfitCentreByDimValueForCrewPayroll(string dimValue)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueProfitCentreByDimValueForCrewPayroll(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimValue;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueCostCentreByProfitCentreDimValAndVesselDimVal(string pcdim_val, string vesselDimVal)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueCostCentreByProfitCentreAndVessel(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@profitCentre", SqlDbType.NVarChar).Value = pcdim_val.Trim();
                cmd.Parameters.Add("@vesselDimVal", SqlDbType.NVarChar).Value = vesselDimVal.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueCostCentreByProfitCentreDimValAndVesselName(string pcdim_val, string vesselNAme)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueCostCentreByProfitCentreDimValAndVesselName(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@profitCentre", SqlDbType.NVarChar).Value = pcdim_val.Trim();
                cmd.Parameters.Add("@vesselNAme", SqlDbType.NVarChar).Value = vesselNAme.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueCostCentreByDimVal(string dim_val)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueCostCentreByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dim_val.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueByDimVal(string dim_val)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueLegalEntityByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dim_val.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetAglDimValueLegalEntityByCostCentreDimVal(string cost_centre_dim_val)
        {
            agldimvalue costCenter = GetAglDimValueCostCentreByDimVal(cost_centre_dim_val);
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueLegalEntityByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = costCenter.rel_value.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetICVesselDimValue(string dim_value)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueICVesselByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dim_value.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
        public agldimvalue GetICVoyageDimValue(string dim_value)
        {
            agldimvalue result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(AgressoSQL.GetAglDimValueICVoyageByDimVal(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dim_value.Trim();
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new agldimvalue(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
            return result;
        }
    }
}
